/* 2025年6月24日09:17:42
 * 想做一个高级的权限功能，并以这个权限系统为依托，让项目完善。
 * User、Role、Department、Page、PageWithControl、PageWithMenu(这个可能会省略)
 */

/**/
create database QiAuthBase
go
use QiAuthBase
go



/*  **********************************
	批量按顺序删除数据库，便于数据库初始化操作。2025年6月24日10:54:36
	**********************************
	
	drop table [XXXXXXXXX]



	drop table [Log]
	drop table [ThirdPartyEntrance]
	drop table [UserDetail]
	drop table [RegistUser]
	drop table [Department]
	drop table [RoleComparePageControl]
	drop table [Role]
	drop table [PageWithMenu]
	drop table [PageWithControl]
	drop table [Page]


	**********************************
    **********************************   */



--页面
create table [Page]
(
	[Id] varchar(200) primary key,--使用长串字符表示，不易被猜到。
	[Name] varchar(200),
	[Description] varchar(400),
	[Path] varchar(400),
	[CreateTime] datetime,
	[UpdateTime] datetime,
)
go
--页面对应的功能
--如果一个页面没有关联这个规则，就随便看。
--如果关联的规则，就要在角色表中做勾选保存，以便于角色有对于的权限。
create table [PageWithControl]
(
	[Id] varchar(200) primary key,--使用长串字符表示，不易被猜到。
	[Name] varchar(200),--规则名称，如：查看全部、查看自己及下属、仅查看本部门、仅看自己、创建数据、修改数据、删除数据
	[Description] varchar(400),
	[CreateTime] datetime,
	[UpdateTime] datetime,
	[PageId] varchar(200) references [Page]([Id]),
)
go
--导航菜单
create table [PageWithMenu]
(
	[Id] varchar(200) primary key,--使用长串字符表示，不易被猜到。
	[ParentId] varchar(200),--父级ID，空则为根节点。
	[Name] varchar(200),
	[CreateTime] datetime,
	[UpdateTime] datetime,
	[PageId] varchar(200) references [Page]([Id]),--我想，菜单也是分配多级的，
)
go

--角色
create table [Role]
(
	[Id] varchar(200) primary key,--使用长串字符表示，不易被猜到。
	[Name] varchar(200),
	[Description] varchar(400),
	[CreateTime] datetime,
	[UpdateTime] datetime,
)
go
--角色与页面权限的对应
create table [RoleComparePageControl]
(
	[Id] varchar(200) primary key,--使用长串字符表示，不易被猜到。
	[RoleId] varchar(200) references [Role](Id),
	[PageWithControlId] varchar(200) references [PageWithControl](Id),
	[CreateTime] datetime,
	[UpdateTime] datetime,
)
go
--部门上下级关系(递归关系)
create table [Department]
(
	[Id] varchar(200) primary key,--使用长串字符表示，不易被猜到。
	[ParentId] varchar(200),--父级ID，空则为根节点。
	[Name] varchar(200),
	[Description] varchar(400),
	[CreateTime] datetime,
	[UpdateTime] datetime,
	[Path] varchar(500),--路径要定时更新，每次对 这个部门表进行 新增/修改/删除时，要把整个表的路径更新一下。
			/* 部门层级查询 ： 
			Department 表通过 ParentId 实现递归层级（如“公司→部门→子部门”），
			但复杂层级查询需依赖递归 SQL（如 CTE），
			对大数据量场景可能存在性能瓶颈，
			可考虑增加 Path 字段（如 0/1/3 ）加速层级查询。*/
)
go




--用户表
create table [RegistUser]
(
	[Id] varchar(200) primary key,--使用长串字符表示，不易被猜到。
	[LoginName] varchar(200),
	[LoginPassword] varchar(200),--使用加密存储，保护用户隐私。
	[CreateTime] datetime,
	[UpdateTime] datetime,
	[RoleId] varchar(200) references [Role]([Id]),
	[DepartmentId] varchar(200) references [Department]([Id]),
)
go
--用户详细属性
create table [UserDetail]
(
	[Id] varchar(200) primary key,--使用长串字符表示，不易被猜到。
	[Key] varchar(200),--关键字
	[Value] varchar(400),
	[RegistUserId] varchar(200) foreign key references [RegistUser]([Id]),
	[CreateTime] datetime,
	[UpdateTime] datetime,
)
go
--第三方入口
create table [ThirdPartyEntrance]
(
	[Id] varchar(200) primary key,--使用长串字符表示，不易被猜到。
	[EntranceType] varchar(200),--数据来源类型，比如：手机号、邮箱、微信、微博等。
	[Key] varchar(200),--关键字
	[BackValue] varchar(400),--API反馈字符
	[RegistUserId] varchar(200) foreign key references [RegistUser]([Id]),--不管是哪家第三方登录，最终都是要与我们自己的用户表挂钩的。
	[CreateTime] datetime,
	[UpdateTime] datetime,
)
go

--操作日志
create table [Log]
(
	[Id] varchar(200) primary key,--使用长串字符表示，不易被猜到。
	[Key] varchar(200),--关键字,比如：登录、注册、创建数据、修改数据、删除数据、查看数据、系统故障等。
	[Value] varchar(400),
	[RegistUserId] varchar(200) foreign key references [RegistUser]([Id]),
	[CreateTime] datetime,
)
go



/*
### 数据库设计合理性分析
用户提供的 d:\work\code\NewAuthBase\关于新权限系统的数据库.sql 脚本设计了一套基于角色的权限管理（RBAC）数据库，整体结构符合核心需求，以下从多维度分析其合理性：
 1. 表结构与关系设计
- 符合RBAC模型 ：通过 Role （角色）→ RoleComparePageControl （角色-页面功能关联）→ PageWithControl （页面功能）→ Page （页面）的层级关系，实现了“角色→权限→页面”的权限控制链，符合经典RBAC模型。
- 外键约束完整 ：所有关联表（如 PageWithControl.PageId 引用 Page.Id 、 RoleComparePageControl.RoleId 引用 Role.Id 等）均通过外键约束保证数据一致性，避免脏数据。
- 部门层级优化 ： Department 表新增 Path 字段（如 0/1/3 ），通过预存层级路径优化复杂层级查询，避免了递归SQL的性能瓶颈，设计合理。 2. 字段设计合理性
- 主键策略 ：所有表主键使用 varchar(200) 存储长字符串（如GUID），避免主键被猜测，提升安全性（但 varchar(200) 长度略冗余，通常GUID仅需36字符，可优化为 varchar(50) 以提升索引效率）。
- 敏感信息保护 ： RegistUser.LoginPassword 明确说明“加密存储”，符合安全规范。
- 时间字段完善 ：所有核心表均包含 CreateTime （创建时间）和 UpdateTime （更新时间），便于数据追踪与审计。 3. 潜在优化建议
- 索引补充 ：高频查询字段（如 RegistUser.LoginName 、 Page.Path 、 Department.Path ）建议添加索引，提升查询效率。
- 字段长度优化 ：部分字段（如 Page.Path 、 Department.Path ）可根据实际业务需求调整长度，减少存储空间占用（例如 Department.Path 调整为 varchar(400) 已足够）。
- 维护逻辑明确 ： Department.Path 字段需通过触发器或应用层逻辑自动更新（如新增/修改部门时），建议在注释或文档中补充具体维护方式，确保数据准确性。 总结
该数据库设计整体合理，覆盖了权限系统的核心功能（用户、角色、页面、权限关联），外键约束和字段设计符合规范。针对高频查询和存储效率的优化建议可进一步提升系统性能。
*/



--2025年6月24日14:08:34 接下来，我要做测试数据了。


/* 
  select * from [Page] order by [CreateTime]
*/
insert into [Page] values('d0cd0c75-67ec-4033-8129-bd2708359104','首页','综合业务逻辑，集中汇总显示到该页面。','/Index',GETDATE(),GETDATE())
insert into [Page] values('fd31dca5-5cd1-4f31-b4dd-4239ac738728','功能测试页1','','/Test/Test1',GETDATE(),GETDATE())
insert into [Page] values('7bec1560-3871-41a3-ba20-d3557d1d48b2','功能测试页2','','/Test/Test2',GETDATE(),GETDATE())
insert into [Page] values('cb09ba9f-2753-42f1-ba4c-73ebf208fc50','功能测试页3','','/Test/Test3',GETDATE(),GETDATE())
insert into [Page] values('f0003e55-1352-4a1b-becf-df3b108d1683','功能测试页4','','/Test/Test3',GETDATE(),GETDATE())
insert into [Page] values('a1fe7cff-dab3-4b8e-82a7-49c0fb5beca3','功能测试页5','','/Test/Test3',GETDATE(),GETDATE())
insert into [Page] values('b0e46c0c-3dad-4efd-8107-b08af45ee05f','功能测试页6','','/Test/Test3',GETDATE(),GETDATE())


/* 
  select * from [PageWithControl] order by [CreateTime]
*/
insert into [PageWithControl] values('00971728-1ab9-4d2b-813d-72d62c5f9a1e','查看全部','完整数据不受限制',GETDATE(),GETDATE(),'d0cd0c75-67ec-4033-8129-bd2708359104')
insert into [PageWithControl] values('972d69b2-8436-45f0-852c-04830163e62c','查看自己及下属','依据所在部门查询人员ID，那些人创建的数据可见。',GETDATE(),GETDATE(),'d0cd0c75-67ec-4033-8129-bd2708359104')
insert into [PageWithControl] values('632f4b60-dbf5-4fc2-be77-f0633d8f8790','仅查看本部门','依据所在部门查询人员ID，那些人创建的数据可见。',GETDATE(),GETDATE(),'d0cd0c75-67ec-4033-8129-bd2708359104')
insert into [PageWithControl] values('c77a9701-48d8-4d8a-a5bd-de36206f6ae7','仅看自己','我的数据我自己看',GETDATE(),GETDATE(),'d0cd0c75-67ec-4033-8129-bd2708359104')
insert into [PageWithControl] values('050a6593-e4e7-4224-95d3-906f87650f18','创建数据','有创建数据的权限',GETDATE(),GETDATE(),'d0cd0c75-67ec-4033-8129-bd2708359104')
insert into [PageWithControl] values('bae31f59-83ef-4a10-9fd0-916eb24d118a','修改数据','有修改数据的权限',GETDATE(),GETDATE(),'d0cd0c75-67ec-4033-8129-bd2708359104')
insert into [PageWithControl] values('e627e0ac-93e3-4225-9f38-9d13b8f9911e','删除数据','有删除数据的权限',GETDATE(),GETDATE(),'d0cd0c75-67ec-4033-8129-bd2708359104')


/* 
  select * from [PageWithMenu] order by [CreateTime]
*/
insert into [PageWithMenu] values('c2667b82-251c-43f9-a9bf-6341038d37e3',null,'菜单入口',GETDATE(),GETDATE(),null)
insert into [PageWithMenu] values('74b56cc7-07d3-4e8d-ab88-fe49ba4b7e21','c2667b82-251c-43f9-a9bf-6341038d37e3','首页',GETDATE(),GETDATE(),'d0cd0c75-67ec-4033-8129-bd2708359104')
insert into [PageWithMenu] values('715f0e3a-a6e0-4c5c-9b6d-2204e7dcfd86','c2667b82-251c-43f9-a9bf-6341038d37e3','功能块测试',GETDATE(),GETDATE(),null)
insert into [PageWithMenu] values('94ccd758-2b13-4d0d-88db-365ccaad2860','715f0e3a-a6e0-4c5c-9b6d-2204e7dcfd86','功能页1',GETDATE(),GETDATE(),'fd31dca5-5cd1-4f31-b4dd-4239ac738728')
insert into [PageWithMenu] values('c9eda8be-c421-4cbe-af7e-747d91234427','715f0e3a-a6e0-4c5c-9b6d-2204e7dcfd86','功能页2',GETDATE(),GETDATE(),'7bec1560-3871-41a3-ba20-d3557d1d48b2')
insert into [PageWithMenu] values('16f1be4c-e2ea-42cd-8e23-5bb381c29b89','715f0e3a-a6e0-4c5c-9b6d-2204e7dcfd86','功能页3',GETDATE(),GETDATE(),'cb09ba9f-2753-42f1-ba4c-73ebf208fc50')
insert into [PageWithMenu] values('9b23351b-897d-4fc0-bc5b-8180e9bd9d7a','715f0e3a-a6e0-4c5c-9b6d-2204e7dcfd86','功能页4',GETDATE(),GETDATE(),'f0003e55-1352-4a1b-becf-df3b108d1683')
insert into [PageWithMenu] values('56ce0ab1-f00d-48e8-a44c-e8a03964d50e','715f0e3a-a6e0-4c5c-9b6d-2204e7dcfd86','功能页5',GETDATE(),GETDATE(),'a1fe7cff-dab3-4b8e-82a7-49c0fb5beca3')
insert into [PageWithMenu] values('c5efb353-43ab-491a-b7b5-d5fe86a8806f','715f0e3a-a6e0-4c5c-9b6d-2204e7dcfd86','功能页6',GETDATE(),GETDATE(),'b0e46c0c-3dad-4efd-8107-b08af45ee05f')


/* 
  select * from [Role] order by [CreateTime]
*/
insert into [Role] values('f5f30251-8ace-47cc-a5ba-201d1c3032bf','超级管理员','权限最大',GETDATE(),GETDATE())
insert into [Role] values('3d8b2d03-9b0b-47c0-92e5-29d5b163fcd8','教务管理员','权限第二',GETDATE(),GETDATE())
insert into [Role] values('b0e1f349-4beb-4820-a67b-5433aff848a6','教师','教师权限',GETDATE(),GETDATE())
insert into [Role] values('23a9a427-dade-40eb-8017-b1d7d82b5771','学员','学员权限',GETDATE(),GETDATE())
insert into [Role] values('f2dd72a6-d6bc-4f40-beec-867136f27462','游客','权限最小',GETDATE(),GETDATE())



/* 
  select * from [RoleComparePageControl] order by [CreateTime]
*/
insert into [RoleComparePageControl] values('29bc5cec-c545-4342-96b5-9ba4e9c31674','f5f30251-8ace-47cc-a5ba-201d1c3032bf','00971728-1ab9-4d2b-813d-72d62c5f9a1e',GETDATE(),GETDATE())




/* 
  select * from [Department] order by [CreateTime]
*/
insert into [Department] values('cd70a6e8-541d-4a5f-a19b-cdb21bbe3721',null,'某某公司','根节点',GETDATE(),GETDATE(),'');
insert into [Department] values('02ba8d57-acd2-4095-aa0e-ddc7e4126eef','cd70a6e8-541d-4a5f-a19b-cdb21bbe3721','系统维护部','管理员一般都在这里',GETDATE(),GETDATE(),'');
insert into [Department] values('e8a1cee4-b892-4e91-8df0-7798cfb99640','cd70a6e8-541d-4a5f-a19b-cdb21bbe3721','教务部','管理岗的教师在这里',GETDATE(),GETDATE(),'');
insert into [Department] values('3e98c160-3049-439a-8a1e-548da24cc0d3','cd70a6e8-541d-4a5f-a19b-cdb21bbe3721','教学部','授课教师在这里',GETDATE(),GETDATE(),'');
insert into [Department] values('c41e4347-290a-46e7-96af-3ac450149cfe','cd70a6e8-541d-4a5f-a19b-cdb21bbe3721','学员部','学生在这里',GETDATE(),GETDATE(),'');



/* 
  select * from [RegistUser] order by [CreateTime]
*/
insert into [RegistUser] values('dd66bbe0-6831-42a6-90f4-1fb78c8f33c5','admin','admin',GETDATE(),GETDATE(),'f5f30251-8ace-47cc-a5ba-201d1c3032bf','02ba8d57-acd2-4095-aa0e-ddc7e4126eef')


/* 
  select * from [UserDetail] order by [CreateTime]
*/
insert into [UserDetail] values('cdc2b348-e435-4695-89a1-8839fa5a83ef','姓名','qizhuhua','dd66bbe0-6831-42a6-90f4-1fb78c8f33c5',GETDATE(),GETDATE())
insert into [UserDetail] values('87988658-f417-4689-a9f8-e1be4b0cc1d4','性别','男','dd66bbe0-6831-42a6-90f4-1fb78c8f33c5',GETDATE(),GETDATE())
insert into [UserDetail] values('03ffd0d1-c303-4bc6-a5a2-a625de52b0ab','邮箱','an1ju@163.com','dd66bbe0-6831-42a6-90f4-1fb78c8f33c5',GETDATE(),GETDATE())
insert into [UserDetail] values('c576315e-66ee-413c-bc8b-33696987b1a8','身份证','21010519810530xxxx','dd66bbe0-6831-42a6-90f4-1fb78c8f33c5',GETDATE(),GETDATE())
insert into [UserDetail] values('8235baad-ee33-4d28-9d2a-8671cc4306df','家庭住址','辽宁省沈阳市皇姑区天山路xxxx','dd66bbe0-6831-42a6-90f4-1fb78c8f33c5',GETDATE(),GETDATE())
insert into [UserDetail] values('6dcfa2f8-4005-48a6-be25-c2ebb7a6843e','联系方式','13940280000','dd66bbe0-6831-42a6-90f4-1fb78c8f33c5',GETDATE(),GETDATE())
